package com.sduept.nwld.dataserver.util;

import java.io.File;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.lang.reflect.Method;
import java.sql.Timestamp;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.Date;
import java.util.LinkedList;
import java.util.List;
import java.util.Map;
import java.util.Set;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.usermodel.BorderStyle;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.ss.usermodel.VerticalAlignment;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFCellStyle;
import org.apache.poi.xssf.usermodel.XSSFFont;
import org.apache.poi.xssf.usermodel.XSSFRichTextString;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import com.sduept.bigdata.fault.manager.DeviceFaultCountManager.YearCountInfo;
import com.sduept.bigdata.fault.model.FaultSourceCountDTO;
import com.sduept.cim.ledger.manager.EquipmentCountManager.EquipmentCountDto;
import com.sduept.nwld.dataview.vo.fault.FaultSourceCountVO;
import com.sduept.nwld.dataview.vo.fault.FaultSourceCountVO.UnitCountInfo;
import com.sduept.nwld.fault.analysis.model.FaultSourceInfoDTO;
import com.sduept.nwld.fault.analysis.model.FaultUploadInfoDTO;
import com.sduept.nwld.fault.analysis.model.StationFaultUploadInfoDTO;
import com.sduept.utils.DateUtil;

public class ExcelUtil {
	XSSFWorkbook excelWorkBook = new XSSFWorkbook();
	private static HSSFCellStyle redStyle;
	private static HSSFCellStyle greenStyle;
	private static HSSFCellStyle headerStyle;
	private static HSSFCellStyle contentStyle;

	/**
	 * 自定义当单元格格式为数值类型时，也可以正确解析
	 * @param inputStream
	 * @param ingnoreRowCount
	 * @return
	 * @throws IOException
	 */
	public static List<List<Object>> readExcelDefine(InputStream inputStream, int ingnoreRowCount) throws IOException {
		List<List<Object>> list = new LinkedList<List<Object>>();
		// 构造 XSSFWorkbook 对象，strPath 传入文件路径
		XSSFWorkbook xwb = new XSSFWorkbook(inputStream);
		// 读取第一章表格内容
		XSSFSheet sheet = xwb.getSheetAt(0);
		String value = "";
		XSSFRow row = null;
		XSSFCell cell = null;
		for (int i = sheet.getFirstRowNum() + ingnoreRowCount; i <= sheet.getPhysicalNumberOfRows(); i++) {
			row = sheet.getRow(i);
			if (row == null) {
				continue;
			}
			List<Object> linked = new LinkedList<Object>();
			for (int j = row.getFirstCellNum(); j <= row.getLastCellNum(); j++) {
				cell = row.getCell(j);
				if (cell == null) {
					linked.add(new String(""));
					continue;
				}
				value = formatCell(cell);
				linked.add(value);
			}
			list.add(linked);
		}
		xwb.close();
		return list;
	}

	public static String formatCell(XSSFCell cell) {
		String value = null;
		switch (cell.getCellType()) {
		// 数值型，0
		case XSSFCell.CELL_TYPE_NUMERIC:
			if (HSSFDateUtil.isCellDateFormatted(cell)) {
				//如果是date类型则 ，获取该cell的date值 
				Date date = HSSFDateUtil.getJavaDate(cell.getNumericCellValue());
				SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
				value = format.format(date);
			} else {// 纯数字
				//value = (new BigDecimal(cell.getNumericCellValue())).toString();
				value = String.valueOf(cell.getNumericCellValue());
				//解决1234.0  去掉后面的.0
				if(null != value && !"".equals(value.trim())){
					String[] item = value.split("[.]");  
					if(1 < item.length && "0".equals(item[1])){
						value = item[0];
					}
				}
			}
			break;
		// 字符串型，1
		case XSSFCell.CELL_TYPE_STRING:
			value = cell.getStringCellValue().toString();
			break;
		//公式类型，2
		case XSSFCell.CELL_TYPE_FORMULA:
			//读公式计算值  
            value = String.valueOf(cell.getNumericCellValue());  
            if (value.equals("NaN")) {// 如果获取的数据值为非法值,则转换为获取字符串  
                value = cell.getStringCellValue().toString();  
            }  
            break;
		// 空值，3
		case XSSFCell.CELL_TYPE_BLANK:
			value = "";
			break;
		// 布尔型，4
		case XSSFCell.CELL_TYPE_BOOLEAN:
			value = ""+cell.getBooleanCellValue();
			break;
		//错误，5
		case XSSFCell.CELL_TYPE_ERROR:
			value = "";
			break;
		default:
			value = cell.getStringCellValue();
		}
		return value;
	}

	/**
	 * 读取Office 2007 excel
	 * */
	public static List<List<Object>> readExcel(InputStream inputStream,
			int ingnoreRowCount) throws IOException {
		List<List<Object>> list = new LinkedList<List<Object>>();
		// 构造 XSSFWorkbook 对象，strPath 传入文件路径
		XSSFWorkbook xwb = new XSSFWorkbook(inputStream);
		// 读取第一章表格内容
		XSSFSheet sheet = xwb.getSheetAt(0);
		Object value = null;
		XSSFRow row = null;
		XSSFCell cell = null;
		for (int i = sheet.getFirstRowNum() + ingnoreRowCount; i <= sheet.getPhysicalNumberOfRows(); i++) {
			row = sheet.getRow(i);
			if (row == null) {
				continue;
			}
			List<Object> linked = new LinkedList<Object>();
			for (int j = row.getFirstCellNum(); j <= row.getLastCellNum(); j++) {
				cell = row.getCell(j);
				if (cell == null) {
					linked.add(new String(""));
					continue;
				}
				DecimalFormat df = new DecimalFormat("#0.00");// 格式化 number// String// 字符
				SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");// 格式化日期字符串
				switch (cell.getCellType()) {
				case XSSFCell.CELL_TYPE_STRING:
					value = cell.getStringCellValue();
					break;
				case XSSFCell.CELL_TYPE_NUMERIC:
					if ("@".equals(cell.getCellStyle().getDataFormatString())) {
						value = df.format(cell.getNumericCellValue());
					} else if ("General".equals(cell.getCellStyle().getDataFormatString())) {
						value = df.format(cell.getNumericCellValue());
					} else {
						value = sdf.format(HSSFDateUtil.getJavaDate(cell
								.getNumericCellValue()));
					}
					break;
				case XSSFCell.CELL_TYPE_BOOLEAN:
					value = cell.getBooleanCellValue();
					break;
				case XSSFCell.CELL_TYPE_BLANK:
					value = new String("");
					break;
				default:
					value = cell.toString();
				}
				linked.add(value);
			}
			list.add(linked);
		}
		xwb.close();
		return list;
	}

	/**
	 * @param excelHeaders
	 *            excel表头
	 * @param header_field_map
	 *            excel表头对应对象的属性
	 * @param results
	 *            对象集合
	 */
	public void exportExcel(List<String> excelHeaders,
			Map<String, String> header_field_map,
			List<? extends Object> results, String fileName, String title) {
		// 0.创建工作本
		// 1.创建表
		XSSFSheet excelSheet = null;
		excelSheet = excelWorkBook.createSheet("sheet1");
		excelSheet.setDefaultColumnWidth(30);
		// 设置字体
		XSSFFont font = excelWorkBook.createFont();
		font.setBold(true);
		font.setFontName("宋体");
		font.setFontHeightInPoints((short) 18);
		XSSFCellStyle cellStyle = excelWorkBook.createCellStyle();
		cellStyle.setFont(font);
		cellStyle.setAlignment(HorizontalAlignment.CENTER);
		// 第一行 标题
		// 在索引0的位置创建行（最顶端的行）
		XSSFRow row = excelSheet.createRow(0);
		// 在索引0的位置创建单元格（左上端）
		XSSFCell cell = row.createCell(0);
		// 定义单元格为字符串类型
		cell.setCellType(XSSFCell.CELL_TYPE_STRING);
		cell.setCellStyle(cellStyle);
		// 在单元格中输入一些内容
		cell.setCellValue(new XSSFRichTextString(title));
		CellRangeAddress cellRangeAddress = new CellRangeAddress(0, 0, 0,
				excelHeaders.size() - 1);
		if (cellRangeAddress.getNumberOfCells() >= 2) {
			excelSheet.addMergedRegion(cellRangeAddress);
		}
		// 2.创建表头: 创建一行
		XSSFRow headerRow = excelSheet.createRow(1);
		// 设置字体
		XSSFFont font2 = excelWorkBook.createFont();
		font2.setFontName("宋体");
		font2.setBold(true);// 粗体显示
		font2.setFontHeightInPoints((short) 10);
		for (int i = 0; i < excelHeaders.size(); i++) {
			// 创建一个单元格
			XSSFCell headerCell = headerRow.createCell(i);
			// 设置cell的值
			headerCell.setCellValue(excelHeaders.get(i));
			XSSFCellStyle setFont = excelWorkBook.createCellStyle();
			setFont.setFont(font2);
			setFont.setWrapText(true);
			setFont.setAlignment(HorizontalAlignment.CENTER); // 居中
			setFont.setBorderBottom(BorderStyle.THIN); // 下边框
			setFont.setBorderLeft(BorderStyle.THIN);// 左边框
			setFont.setBorderTop(BorderStyle.THIN);// 上边框
			setFont.setBorderRight(BorderStyle.THIN);// 右边框
			headerCell.setCellStyle(setFont);
		}
		// 3.根据查询出来的结果集results，填写excel表格
		if (results != null) {
			for (int index = 0; index < results.size(); index++) {
				// 4.创建一行
				XSSFRow tableRow = excelSheet.createRow((short) index + 2); // 创建行，因为第一行是表头,
				// 即row(0)
				Object obj = results.get(index);
				fillTableCellsForObject(tableRow, obj, excelHeaders,
						header_field_map);
			}
		}
		// 4.将excel导出到文件中
		FileOutputStream out = null;
		// 如果没有名字则文件名为data时间+excelName
		try {
			File file = new File(fileName);
			file.createNewFile();
			out = new FileOutputStream(file);
			excelWorkBook.write(out);
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				out.close();
				excelWorkBook.close();
			} catch (IOException e) {
				e.printStackTrace();
			}
		}

	}

	private void fillTableCellsForObject(XSSFRow tableRow, Object obj,
			List<String> excelHeaders, Map<String, String> header_field_map) {
		boolean flag = false;// 控制序号居中显示
		if (excelHeaders != null && excelHeaders.size() > 0) {
			for (int i = 0; i < excelHeaders.size(); i++) {
				String field = header_field_map.get(excelHeaders.get(i));// 获得表头对应对象的属性
				Method[] methods = obj.getClass().getMethods();
				for (Method m : methods) {
					if (m.getName().equalsIgnoreCase("get" + field)) {
						try {
							Object cellValue = m.invoke(obj, null);
							XSSFCell positionCell = tableRow.createCell(i);
							if (cellValue instanceof String) {
								XSSFCellStyle style = cellStyle(flag, cellValue);
								positionCell.setCellStyle(style);
								positionCell.setCellValue((String) cellValue);
							} else if (cellValue instanceof Timestamp) {
								XSSFCellStyle style = cellStyle(flag, cellValue);
								positionCell.setCellStyle(style);
								try {
									positionCell
											.setCellValue(DateUtil.getStringDate(
													DateUtil.getTimestamp(
															cellValue
																	.toString(),
															DateUtil.pattern_ymdhms),
													DateUtil.pattern_ymd));
								} catch (Exception e) {
									e.printStackTrace();
								}
							} else {
								XSSFCellStyle style = cellStyle(flag, cellValue);
								positionCell.setCellStyle(style);
								positionCell.setCellValue(String
										.valueOf(cellValue));
							}
						} catch (Exception e) {
							e.printStackTrace();
						}
						break;
					}
				}
			}
		}
	}

	private XSSFCellStyle cellStyle(boolean flag, Object cellValue) {
		XSSFCellStyle style = excelWorkBook.createCellStyle();
		style.setBorderBottom(BorderStyle.THIN); // 下边框
		style.setBorderLeft(BorderStyle.THIN);// 左边框
		style.setBorderTop(BorderStyle.THIN);// 上边框
		style.setBorderRight(BorderStyle.THIN);// 右边框
		style.setVerticalAlignment(VerticalAlignment.CENTER);// 垂直居中
		if ((!flag && cellValue instanceof String)
				|| (!flag && cellValue instanceof List)) {
			style.setAlignment(HorizontalAlignment.LEFT); // 水平居左
		} else if ((!flag && cellValue instanceof Timestamp)
				|| (flag && cellValue instanceof String)) {
			style.setAlignment(HorizontalAlignment.CENTER); // 水平居中
		} else if (!flag && cellValue instanceof Integer) {
			// style.setAlignment(XSSFCellStyle.ALIGN_RIGHT); // 水平居右
			// TODO 已消缺 未消缺 消缺中 虽然类型为Integer，但导入Excel中的内容为String
			style.setAlignment(HorizontalAlignment.LEFT); // 水平居左
		}
		style.setWrapText(true);
		return style;
	}

	/**
	 * 自定义一次设备统计excel
	 */
	public static void writeEquipmentStatisExcel(Map<String, List<EquipmentCountDto>> dtos, FileOutputStream fos) {
		try {
			// 1.创建工作簿
			HSSFWorkbook workbook = new HSSFWorkbook();
			contentStyle = createCellStyle(workbook, (short) 11, false, true);
			headerStyle = createCellStyle(workbook, (short) 11, true, true);
			writeEquipmentStatis(workbook, dtos);
			workbook.write(fos);
			workbook.close();
		} catch (Exception e) {
			e.printStackTrace();
		}
	}
	
	/**
	 * 一次设备统计 sheets、汇总数据需单独处理
	 */
	private static void writeEquipmentStatis(HSSFWorkbook workbook,Map<String, List<EquipmentCountDto>> dtos) {
		List<CellRangeAddress> headers = new ArrayList<CellRangeAddress>();
		headers.add(new CellRangeAddress(0, 1, 0, 1)); // 起始行,结束行,起始列,结束列
		headers.add(new CellRangeAddress(0, 1, 2, 3));
		headers.add(new CellRangeAddress(0, 0, 4, 5));
		headers.add(new CellRangeAddress(0, 0, 6, 7));
		headers.add(new CellRangeAddress(0, 1, 8, 8));
		headers.add(new CellRangeAddress(0, 1, 9, 9));
		headers.add(new CellRangeAddress(0, 0, 10, 11));
		
		Set<String> keySet = dtos.keySet();
		for (String key : keySet) {
			List<EquipmentCountDto> dto = dtos.get(key);
			String t = key;
			if (!"全网".equals(key)) {
				t+="kV";
			}
			HSSFSheet sheet = workbook.createSheet(t);
			//表头
			for (CellRangeAddress ra : headers) {
				sheet.addMergedRegion(ra);
			}
			sheet.setDefaultColumnWidth(10);
			sheet.setDefaultRowHeightInPoints(20F);
			HSSFRow row1 = sheet.createRow(0);
			List<String>title;
			List<String>title2;
			if (key.contains("500") || key.equals("全网")) {	//只有500kV才展示直流线路、换流站信息
				title = Arrays.asList("单位","", "变电站", "", "变压器","","交流线","", "换流站台数", "换流站座数","直流线路","");
				title2 = Arrays.asList("","","","","数量","容量","数量","长度","","","数量","长度");
			}else{
				title = Arrays.asList("单位","", "变电站", "", "变压器","","交流线","");
				title2 = Arrays.asList("","","","","数量","容量","数量","长度");
			}
			for (int i = 0; i < title.size(); i++) {
				HSSFCell cell2 = row1.createCell(i);
				cell2.setCellStyle(headerStyle);
				cell2.setCellValue(title.get(i));
			}

			HSSFRow row2 = sheet.createRow(1);
			for (int i = 0; i < title2.size(); i++) {
				HSSFCell cell2 = row2.createCell(i);
				cell2.setCellStyle(headerStyle);
				cell2.setCellValue(title2.get(i));
			}
			//内容
			if (null != dto) {
				int i = 1;
				List<CellRangeAddress> rows = new ArrayList<CellRangeAddress>();
				for (EquipmentCountDto d : dto) {
					i++;
					rows.add(new CellRangeAddress(i, i, 0, 1));
					rows.add(new CellRangeAddress(i, i, 2, 3));

					HSSFRow row = sheet.createRow(i);
					HSSFCell cell_0 = row.createCell(0);
					cell_0.setCellValue(d.getRank());
					cell_0.setCellStyle(contentStyle);

					HSSFCell cell_00 = row.createCell(1);
					cell_00.setCellValue("");
					cell_00.setCellStyle(contentStyle);

					HSSFCell cell_1 = row.createCell(2);
					cell_1.setCellValue(d.getNormalStation().getCount());
					cell_1.setCellStyle(contentStyle);

					HSSFCell cell_11 = row.createCell(3);
					cell_11.setCellValue("");
					cell_11.setCellStyle(contentStyle);
					
					HSSFCell cell_2 = row.createCell(4);
					cell_2.setCellValue(d.getNormalTrans().getCount());
					cell_2.setCellStyle(contentStyle);

					HSSFCell cell_3 = row.createCell(5);
					cell_3.setCellValue(d.getNormalTrans().getCapacity());
					cell_3.setCellStyle(contentStyle);

					HSSFCell cell_4 = row.createCell(6);
					cell_4.setCellValue(d.getAcLine().getCount());
					cell_4.setCellStyle(contentStyle);

					HSSFCell cell_5 = row.createCell(7);
					cell_5.setCellValue(d.getAcLine().getLength());
					cell_5.setCellStyle(contentStyle);

					if (!key.contains("500") && !key.equals("全网")) {
						continue;
					}
					
					HSSFCell cell_6 = row.createCell(8);
					cell_6.setCellValue(d.getConvertStation().getCount());
					cell_6.setCellStyle(contentStyle);

					HSSFCell cell_7 = row.createCell(9);
					cell_7.setCellValue(d.getConvertTrans().getCount());
					cell_7.setCellStyle(contentStyle);

					HSSFCell cell_8 = row.createCell(10);
					cell_8.setCellValue(d.getDcLine().getCount());
					cell_8.setCellStyle(contentStyle);
					
					HSSFCell cell_9 = row.createCell(11);
					cell_9.setCellValue(d.getDcLine().getLength());
					cell_9.setCellStyle(contentStyle);
				}
				for (CellRangeAddress ra : rows) {
					sheet.addMergedRegion(ra);
				}
			}
		}
	}
	
	/**
	 * 自定义故障统计excel
	 */
	public static void writeFaultReportCache(
			List<FaultUploadInfoDTO> lineFault,
			List<FaultUploadInfoDTO> busbarFaultUploadInfoDtos,
			List<FaultUploadInfoDTO> transFaultUploadInfoDtos,
			FileOutputStream fos) {
		try {
			// 1.创建工作簿
			HSSFWorkbook workbook = new HSSFWorkbook();
			redStyle = createRedCellStyle(workbook, (short) 10, false, true);
			greenStyle = creatGreenCellStyle(workbook, (short) 10, false, true);
			contentStyle = createCellStyle(workbook, (short) 10, false, true);
			headerStyle = createCellStyle(workbook, (short) 10, true, true);
			writeLineSheet(workbook, lineFault);
			writeBusSheet(workbook, busbarFaultUploadInfoDtos);
			writeTransFault(workbook, transFaultUploadInfoDtos);
			workbook.write(fos);
			workbook.close();
		} catch (Exception e) {
			e.printStackTrace();
		}
	}
	
	/**
	 * 自定义故障统计excel
	 */
	@Deprecated
	public static void writeFaultReportCache(
			List<FaultUploadInfoDTO> lineFault,
			List<FaultUploadInfoDTO> busbarFaultUploadInfoDtos,
			List<FaultUploadInfoDTO> transFaultUploadInfoDtos,
			List<FaultSourceCountDTO> countList, FileOutputStream fos) {
		try {
			// 1.创建工作簿
			HSSFWorkbook workbook = new HSSFWorkbook();
			redStyle = createRedCellStyle(workbook, (short) 10, false, true);
			greenStyle = creatGreenCellStyle(workbook, (short) 10, false, true);
			contentStyle = createCellStyle(workbook, (short) 10, false, true);
			headerStyle = createCellStyle(workbook, (short) 10, true, true);
			writeLineSheet(workbook, lineFault);
			writeBusSheet(workbook, busbarFaultUploadInfoDtos);
			writeTransFault(workbook, transFaultUploadInfoDtos);
			writeSummarySheet(workbook, countList);
			workbook.write(fos);
			workbook.close();
		} catch (Exception e) {
			e.printStackTrace();
		}
	}

	private static HSSFCellStyle getContentStyle(Integer i) {
		if (null == i || -1 == i) {
			return redStyle;
		} else if (0 == i || 1==i) {
			return greenStyle;
		} else {
			return headerStyle;
		}
	}

	/**
	 * 线路sheet
	 */
	private static void writeLineSheet(HSSFWorkbook workbook,
			List<FaultUploadInfoDTO> lineFault) {
		// 1.1创建合并单元格对象
		List<CellRangeAddress> headers = new ArrayList<CellRangeAddress>();
		// 标题
		headers.add(new CellRangeAddress(0, 1, 0, 1)); // 起始行,结束行,起始列,结束列
		headers.add(new CellRangeAddress(0, 1, 2, 3));
		headers.add(new CellRangeAddress(0, 1, 4, 5));
		headers.add(new CellRangeAddress(0, 1, 6, 6));
		headers.add(new CellRangeAddress(0, 0, 7, 8));
		headers.add(new CellRangeAddress(0, 0, 9, 10));
		headers.add(new CellRangeAddress(0, 0, 11, 12));
		headers.add(new CellRangeAddress(0, 0, 13, 15));
		headers.add(new CellRangeAddress(0, 0, 16, 18));
		headers.add(new CellRangeAddress(0, 0, 19, 20));
		headers.add(new CellRangeAddress(0, 0, 21, 22));
		headers.add(new CellRangeAddress(0, 0, 23, 24));
		headers.add(new CellRangeAddress(0, 0, 25, 26));
		headers.add(new CellRangeAddress(0, 1, 27, 28));
		headers.add(new CellRangeAddress(0, 1, 29, 30));
		headers.add(new CellRangeAddress(0, 1, 31, 32));
		headers.add(new CellRangeAddress(0, 1, 33, 34));
		// 2.创建工作表
		HSSFSheet sheet = workbook.createSheet("线路故障");
		// 2.1加载合并单元格对象
		for (CellRangeAddress ra : headers) {
			sheet.addMergedRegion(ra);
		}
		// 设置默认列宽
		sheet.setDefaultColumnWidth(9);
		sheet.setDefaultRowHeightInPoints(20F);
		// 3.2创建列标题;并且设置列标题
		HSSFRow row2 = sheet.createRow(0);
		String[] titles = { "故障时间", "", "故障情况", "", "厂站", "", "集中录播", "主一保护",
				"", "主二保护", "", "主三保护", "", "断路器保护一", "", "", "断路器保护二", "", "",
				"辅A保护", "", "辅B保护", "", "短引线保护一", "", "短引线保护二","","综合上报完好率","",
				"保护事件完好率","","保护录波完好率","","集中录波完好率","" };
		for (int i = 0; i < titles.length; i++) {
			HSSFCell cell2 = row2.createCell(i);
			// 加载单元格样式
			cell2.setCellStyle(headerStyle);
			cell2.setCellValue(titles[i]);
		}

		HSSFRow rowfour = sheet.createRow(1);
		String[] titlefour = { "", "", "", "", "报文", "录波", "报文", "录波", "报文",
				"录波", "名称", "报文", "录波", "名称", "报文", "录波", "报文", "录波", "报文",
				"录波", "报文", "录波", "报文", "录波","","","","","","","","" };
		for (int i = 0; i < titlefour.length; i++) {
			HSSFCell cell2 = rowfour.createCell(i + 3);
			// 加载单元格样式
			cell2.setCellStyle(contentStyle);
			cell2.setCellValue(titlefour[i]);
		}

		if (lineFault != null) {
			int i = 0;
			int start = 2; // 每一条数据的开始行
			for (int j = 0; j < lineFault.size(); j++) {
				i++;
				FaultUploadInfoDTO dto = lineFault.get(j);
				List<StationFaultUploadInfoDTO> infos = dto.getStationInfo();
				if (infos.size() > 2) {
					for (int k = 0; k < 2; k++) {
						CellRangeAddress r2 = new CellRangeAddress(start, start
								+ infos.size() - 1, k * 2, k * 2 + 1);
						sheet.addMergedRegion(r2);
					}
					for (int k = 13; k < 17; k++) {
						CellRangeAddress r2 = new CellRangeAddress(start,
								start + 2, k*2+1 , k*2 + 2);//此方法参数影响合并行数、列数
						sheet.addMergedRegion(r2);
					}
					start += infos.size();
				} else {
					for (int k = 0; k < 2; k++) {
						CellRangeAddress r2 = new CellRangeAddress(start,
								start + 1, k * 2, k * 2 + 1);
						sheet.addMergedRegion(r2);
					}
					for (int k = 13; k < 17; k++) {
						CellRangeAddress r2 = new CellRangeAddress(start,
								start + 1, k*2+1 , k*2 + 2);
						sheet.addMergedRegion(r2);
					}
					start += 2;
				}

				for (int k = 0; k <= 1; k++) {
					CellRangeAddress r3_1 = new CellRangeAddress(i * 2 + k, i
							* 2 + k, 4, 5); // 厂站
					sheet.addMergedRegion(r3_1);
				}

				if (infos.size() == 0) {
					HSSFRow row0 = sheet.createRow(start - 2); // 内容填充时需要返回到数据的开始行
					HSSFCell cell0 = row0.createCell(0);
					cell0.setCellStyle(contentStyle);
					cell0.setCellValue(DateUtil.getStringDate(
							dto.getFaultTime(), DateUtil.pattern_ymdhms));

					HSSFCell cell1 = row0.createCell(1);
					cell1.setCellStyle(contentStyle);
					cell1.setCellValue("");

					HSSFCell cell2 = row0.createCell(2);
					cell2.setCellStyle(contentStyle);
					cell2.setCellValue(dto.getFaultDesc());

					HSSFCell cell3 = row0.createCell(3);
					cell3.setCellStyle(contentStyle);
					cell3.setCellValue("");
					
					
				}

				for (int k = 0; k < infos.size(); k++) {
					StationFaultUploadInfoDTO info = infos.get(k);
					int n = infos.size();
					if (infos.size() == 1) {
						n = 2;
					}
					HSSFRow row = sheet.createRow(start - n + k);
					HSSFCell cell_0 = row.createCell(0);
					cell_0.setCellValue(DateUtil.getStringDate(
							dto.getFaultTime(), DateUtil.pattern_ymdhms));
					cell_0.setCellStyle(contentStyle);

					HSSFCell cell_1 = row.createCell(1);
					cell_1.setCellValue("");
					cell_1.setCellStyle(contentStyle);

					HSSFCell cell_2 = row.createCell(2);
					cell_2.setCellValue(dto.getFaultDesc());
					cell_2.setCellStyle(contentStyle);

					HSSFCell cell_3 = row.createCell(3);
					cell_3.setCellStyle(contentStyle);
					cell_3.setCellValue("");
					

					HSSFCell cell4 = row.createCell(4);
					cell4.setCellStyle(contentStyle);
					cell4.setCellValue(info.getStationName());

					HSSFCell cell5 = row.createCell(5);
					cell5.setCellStyle(contentStyle);
					cell5.setCellValue("");

					HSSFCell cell6 = row.createCell(6);
					cell6.setCellValue(IntegerToString(info.getWaveInfo()));
					cell6.setCellStyle(getContentStyle(info.getWaveInfo()));

					HSSFCell cell7 = row.createCell(7);
					cell7.setCellValue(IntegerToString(info
							.getMain1DatagramInfo()));
					cell7.setCellStyle(getContentStyle(info
							.getMain1DatagramInfo()));

					HSSFCell cell8 = row.createCell(8);
					cell8.setCellValue(IntegerToString(info.getMain1WaveInfo()));
					cell8.setCellStyle(getContentStyle(info.getMain1WaveInfo()));

					HSSFCell cell9 = row.createCell(9);
					cell9.setCellValue(IntegerToString(info
							.getMain2DatagramInfo()));
					cell9.setCellStyle(getContentStyle(info
							.getMain2DatagramInfo()));

					HSSFCell cell10 = row.createCell(10);
					cell10.setCellValue(IntegerToString(info.getMain2WaveInfo()));
					cell10.setCellStyle(getContentStyle(info.getMain2WaveInfo()));

					HSSFCell cell11 = row.createCell(11);
					cell11.setCellValue(IntegerToString(info
							.getMain3DatagramInfo()));
					cell11.setCellStyle(getContentStyle(info
							.getMain3DatagramInfo()));

					HSSFCell cell12 = row.createCell(12);
					cell12.setCellValue(IntegerToString(info.getMain3WaveInfo()));
					cell12.setCellStyle(getContentStyle(info.getMain3WaveInfo()));

					HSSFCell cell13 = row.createCell(13);
					cell13.setCellValue(info.getBreakerProtect1Name());
					cell13.setCellStyle(contentStyle);

					HSSFCell cell14 = row.createCell(14);
					cell14.setCellValue(IntegerToString(info
							.getBreakerProtect1DatagramInfo()));
					cell14.setCellStyle(getContentStyle(info
							.getBreakerProtect1DatagramInfo()));

					HSSFCell cell15 = row.createCell(15);
					cell15.setCellValue(IntegerToString(info
							.getBreakerProtect1WaveInfo()));
					cell15.setCellStyle(getContentStyle(info
							.getBreakerProtect1WaveInfo()));

					HSSFCell cell16 = row.createCell(16);
					cell16.setCellValue(info.getBreakerProtect2Name());
					cell16.setCellStyle(contentStyle);

					HSSFCell cell17 = row.createCell(17);
					cell17.setCellValue(IntegerToString(info
							.getBreakerProtect2DatagramInfo()));
					cell17.setCellStyle(getContentStyle(info
							.getBreakerProtect2DatagramInfo()));

					HSSFCell cell18 = row.createCell(18);
					cell18.setCellValue(IntegerToString(info
							.getBreakerProtect2WaveInfo()));
					cell18.setCellStyle(getContentStyle(info
							.getBreakerProtect2WaveInfo()));

					HSSFCell cell19 = row.createCell(19);
					cell19.setCellValue(IntegerToString(info
							.getAssistProtect1DatagramInfo()));
					cell19.setCellStyle(getContentStyle(info
							.getAssistProtect1DatagramInfo()));

					HSSFCell cell20 = row.createCell(20);
					cell20.setCellValue(IntegerToString(info
							.getAssistProtect1WaveInfo()));
					cell20.setCellStyle(getContentStyle(info
							.getAssistProtect1WaveInfo()));

					HSSFCell cell21 = row.createCell(21);
					cell21.setCellValue(IntegerToString(info
							.getAssistProtect2DatagramInfo()));
					cell21.setCellStyle(getContentStyle(info
							.getAssistProtect2DatagramInfo()));

					HSSFCell cell22 = row.createCell(22);
					cell22.setCellValue(IntegerToString(info
							.getAssistProtect2WaveInfo()));
					cell22.setCellStyle(getContentStyle(info
							.getAssistProtect2WaveInfo()));

					HSSFCell cell23 = row.createCell(23);
					cell23.setCellValue(IntegerToString(info
							.getShortLeadProtect1DatagramInfo()));
					cell23.setCellStyle(getContentStyle(info
							.getShortLeadProtect1DatagramInfo()));

					HSSFCell cell24 = row.createCell(24);
					cell24.setCellValue(IntegerToString(info
							.getShortLeadProtect1WaveInfo()));
					cell24.setCellStyle(getContentStyle(info
							.getShortLeadProtect1WaveInfo()));

					HSSFCell cell25 = row.createCell(25);
					cell25.setCellValue(IntegerToString(info
							.getShortLeadProtect2DatagramInfo()));
					cell25.setCellStyle(getContentStyle(info
							.getShortLeadProtect2DatagramInfo()));

					HSSFCell cell26 = row.createCell(26);
					cell26.setCellValue(IntegerToString(info
							.getShortLeadProtect2WaveInfo()));
					cell26.setCellStyle(getContentStyle(info
							.getShortLeadProtect2WaveInfo()));
					
					
					HSSFCell cell27 = row.createCell(27);
					cell27.setCellValue(dto.getTotalIntactRate()+"%");
					cell27.setCellStyle(contentStyle);
					
					HSSFCell cell28 = row.createCell(28);
					cell28.setCellValue("");
					cell28.setCellStyle(contentStyle);
					

					HSSFCell cell29 = row.createCell(29);
					cell29.setCellValue(dto.getProtectEventIntactRate()+"%");
					cell29.setCellStyle(contentStyle);
					
					HSSFCell cell30 = row.createCell(30);
					cell30.setCellValue("");
					cell30.setCellStyle(contentStyle);
					
					
					HSSFCell cell31 = row.createCell(31);
					cell31.setCellValue(dto.getProtectWaveIntactRate()+"%");
					cell31.setCellStyle(contentStyle);

					
					HSSFCell cell32 = row.createCell(32);
					cell32.setCellValue("");
					cell32.setCellStyle(contentStyle);
					
					HSSFCell cell33 = row.createCell(33);
					cell33.setCellValue(dto.getCentralWave()+"%");
					cell33.setCellStyle(contentStyle);
					
					HSSFCell cell34 = row.createCell(34);
					cell34.setCellValue("");
					cell34.setCellStyle(contentStyle);
					
				}
			}
		}
	}

	/**
	 * 母线sheet
	 */
	private static void writeBusSheet(HSSFWorkbook workbook,
			List<FaultUploadInfoDTO> busbarFaultUploadInfoDtos) {
		// 1.1创建合并单元格对象
		List<CellRangeAddress> headers = new ArrayList<CellRangeAddress>();
		// 标题
		headers.add(new CellRangeAddress(0, 1, 0, 1)); // 起始行,结束行,起始列,结束列
		headers.add(new CellRangeAddress(0, 1, 2, 3));
		headers.add(new CellRangeAddress(0, 1, 4, 5));
		headers.add(new CellRangeAddress(0, 1, 6, 6));
		headers.add(new CellRangeAddress(0, 0, 7, 8));
		headers.add(new CellRangeAddress(0, 0, 9, 10));
		headers.add(new CellRangeAddress(0, 0, 11, 13));
		headers.add(new CellRangeAddress(0, 0, 14, 16));
		headers.add(new CellRangeAddress(0, 0, 17, 19));
		headers.add(new CellRangeAddress(0, 0, 20, 22));
		headers.add(new CellRangeAddress(0, 0, 23, 25));
		headers.add(new CellRangeAddress(0, 0, 26, 28));
		headers.add(new CellRangeAddress(0, 1, 29, 30));
		headers.add(new CellRangeAddress(0, 1, 31, 32));
		headers.add(new CellRangeAddress(0, 1, 33, 34));
		headers.add(new CellRangeAddress(0, 1, 35, 36));
		// 2.创建工作表
		HSSFSheet sheet = workbook.createSheet("母线故障");
		// 2.1加载合并单元格对象
		for (CellRangeAddress ra : headers) {
			sheet.addMergedRegion(ra);
		}
		// 设置默认列宽
		sheet.setDefaultColumnWidth(9);
		sheet.setDefaultRowHeightInPoints(20F);

		// 3.2创建列标题;并且设置列标题
		HSSFRow row2 = sheet.createRow(0);
		String[] titles = { "故障时间", "", "故障情况", "", "厂站", "", "集中录播", "主一保护",
				"", "主二保护", "", "断路器保护一", "", "", "断路器保护二", "", "", "断路器保护三",
				"", "", "断路器保护四", "", "", "断路器保护五", "", "", "断路器保护六", "", "","综合上报完好率","",
				"保护事件完好率","","保护录波完好率","","集中录波完好率","" };
		for (int i = 0; i < titles.length; i++) {
			HSSFCell cell2 = row2.createCell(i);
			// 加载单元格样式
			cell2.setCellStyle(headerStyle);
			cell2.setCellValue(titles[i]);
		}

		HSSFRow rowfour = sheet.createRow(1);
		String[] titlefour = { "", "", "", "", "报文", "录波", "报文", "录波", "名称",
				"报文", "录波", "名称", "报文", "录波", "名称", "报文", "录波", "名称", "报文",
				"录波", "名称", "报文", "录波", "名称", "报文", "录波","","","","","","","",""  };
		for (int i = 0; i < titlefour.length; i++) {
			HSSFCell cell2 = rowfour.createCell(i + 3);
			// 加载单元格样式
			cell2.setCellStyle(headerStyle);
			cell2.setCellValue(titlefour[i]);
		}

		if (busbarFaultUploadInfoDtos != null) {
			int i = 0;
			int start = 2; // 每一条数据的开始行
			for (int j = 0; j < busbarFaultUploadInfoDtos.size(); j++) {
				i++;
				FaultUploadInfoDTO dto = busbarFaultUploadInfoDtos.get(j);
				List<StationFaultUploadInfoDTO> infos = dto.getStationInfo();
				if (infos.size() > 2) {
					for (int k = 0; k < 2; k++) {
						CellRangeAddress r2 = new CellRangeAddress(start, start
								+ infos.size() - 1, k * 2, k * 2 + 1);
						sheet.addMergedRegion(r2);
					}
					for (int k = 14; k < 19; k++) {
						CellRangeAddress r2 = new CellRangeAddress(start,
								start + 1, k*2+1 , k*2 + 2);
						sheet.addMergedRegion(r2);//TODO  母线
					}
					start += infos.size();
				} else {
					for (int k = 0; k < 2; k++) {
						CellRangeAddress r2 = new CellRangeAddress(start,
								start + 1, k * 2, k * 2 + 1);
						sheet.addMergedRegion(r2);
					}
					for (int k = 14; k < 19; k++) {
						CellRangeAddress r2 = new CellRangeAddress(start,
								start + 1, k*2+1 , k*2 + 2);
						sheet.addMergedRegion(r2);
					}
					start += 2;
				}

				for (int k = 0; k <= 1; k++) {
					CellRangeAddress r3_1 = new CellRangeAddress(i * 2 + k, i
							* 2 + k, 4, 5); // 厂站
					sheet.addMergedRegion(r3_1);
				}

				if (infos.size() == 0) {
					HSSFRow row0 = sheet.createRow(start - 2); // 内容填充时需要返回到数据的开始行
					HSSFCell cell0 = row0.createCell(0);
					cell0.setCellStyle(contentStyle);
					cell0.setCellValue(DateUtil.getStringDate(
							dto.getFaultTime(), DateUtil.pattern_ymdhms));

					HSSFCell cell1 = row0.createCell(1);
					cell1.setCellStyle(contentStyle);
					cell1.setCellValue("");

					HSSFCell cell2 = row0.createCell(2);
					cell2.setCellStyle(contentStyle);
					cell2.setCellValue(dto.getFaultDesc());

					HSSFCell cell3 = row0.createCell(3);
					cell3.setCellStyle(contentStyle);
					cell3.setCellValue("");
				}

				for (int k = 0; k < infos.size(); k++) {
					StationFaultUploadInfoDTO info = infos.get(k);
					int n = infos.size();
					if (infos.size() == 1) {
						n = 2;
					}
					HSSFRow row = sheet.createRow(start - n + k);
					HSSFCell cell_0 = row.createCell(0);
					cell_0.setCellStyle(contentStyle);
					cell_0.setCellValue(DateUtil.getStringDate(
							dto.getFaultTime(), DateUtil.pattern_ymdhms));

					HSSFCell cell_1 = row.createCell(1);
					cell_1.setCellStyle(contentStyle);
					cell_1.setCellValue("");

					HSSFCell cell_2 = row.createCell(2);
					cell_2.setCellStyle(contentStyle);
					cell_2.setCellValue(dto.getFaultDesc());

					HSSFCell cell_3 = row.createCell(3);
					cell_3.setCellStyle(contentStyle);
					cell_3.setCellValue("");

					HSSFCell cell4 = row.createCell(4);
					cell4.setCellStyle(contentStyle);
					cell4.setCellValue(info.getStationName());

					HSSFCell cell5 = row.createCell(5);
					cell5.setCellStyle(contentStyle);
					cell5.setCellValue("");

					HSSFCell cell6 = row.createCell(6);
					cell6.setCellValue(IntegerToString(info.getWaveInfo()));
					cell6.setCellStyle(getContentStyle(info.getWaveInfo()));

					HSSFCell cell7 = row.createCell(7);
					cell6.setCellStyle(getContentStyle(info
							.getMain1DatagramInfo()));
					cell7.setCellValue(IntegerToString(info
							.getMain1DatagramInfo()));

					HSSFCell cell8 = row.createCell(8);
					cell8.setCellValue(IntegerToString(info.getMain1WaveInfo()));
					cell8.setCellStyle(getContentStyle(info.getMain1WaveInfo()));

					HSSFCell cell9 = row.createCell(9);
					cell9.setCellStyle(getContentStyle(info
							.getMain2DatagramInfo()));
					cell9.setCellValue(IntegerToString(info
							.getMain2DatagramInfo()));

					HSSFCell cell10 = row.createCell(10);
					cell10.setCellStyle(getContentStyle(info.getMain2WaveInfo()));
					cell10.setCellValue(IntegerToString(info.getMain2WaveInfo()));

					HSSFCell cell11 = row.createCell(11); // 断路器保护一
					cell11.setCellStyle(contentStyle);
					cell11.setCellValue(info.getBreakerProtect1Name());

					HSSFCell cell12 = row.createCell(12);
					cell12.setCellStyle(getContentStyle(info
							.getBreakerProtect1DatagramInfo()));
					cell12.setCellValue(IntegerToString(info
							.getBreakerProtect1DatagramInfo()));

					HSSFCell cell13 = row.createCell(13);
					cell13.setCellStyle(getContentStyle(info
							.getBreakerProtect1WaveInfo()));
					cell13.setCellValue(IntegerToString(info
							.getBreakerProtect1WaveInfo()));

					HSSFCell cell14 = row.createCell(14); // 断路器保护二
					cell14.setCellStyle(contentStyle);
					cell14.setCellValue(info.getBreakerProtect2Name());

					HSSFCell cell15 = row.createCell(15);
					cell15.setCellStyle(getContentStyle(info
							.getBreakerProtect2DatagramInfo()));
					cell15.setCellValue(IntegerToString(info
							.getBreakerProtect2DatagramInfo()));

					HSSFCell cell16 = row.createCell(16);
					cell16.setCellStyle(getContentStyle(info
							.getBreakerProtect2WaveInfo()));
					cell16.setCellValue(IntegerToString(info
							.getBreakerProtect2WaveInfo()));

					HSSFCell cell17 = row.createCell(17); // 断路器保护三
					cell17.setCellStyle(contentStyle);
					cell17.setCellValue(info.getBreakerProtect3Name());

					HSSFCell cell18 = row.createCell(18);
					cell18.setCellStyle(getContentStyle(info
							.getBreakerProtect3DatagramInfo()));
					cell18.setCellValue(IntegerToString(info
							.getBreakerProtect3DatagramInfo()));

					HSSFCell cell19 = row.createCell(19);
					cell19.setCellStyle(getContentStyle(info
							.getBreakerProtect3WaveInfo()));
					cell19.setCellValue(IntegerToString(info
							.getBreakerProtect3WaveInfo()));

					HSSFCell cell20 = row.createCell(17); // 断路器保护四
					cell20.setCellStyle(contentStyle);
					cell20.setCellValue(info.getBreakerProtect4Name());

					HSSFCell cell21 = row.createCell(18);
					cell21.setCellStyle(getContentStyle(info
							.getBreakerProtect4DatagramInfo()));
					cell21.setCellValue(IntegerToString(info
							.getBreakerProtect4DatagramInfo()));

					HSSFCell cell22 = row.createCell(19);
					cell22.setCellStyle(getContentStyle(info
							.getBreakerProtect4WaveInfo()));
					cell22.setCellValue(IntegerToString(info
							.getBreakerProtect4WaveInfo()));

					HSSFCell cell23 = row.createCell(17); // 断路器保护五
					cell23.setCellStyle(contentStyle);
					cell23.setCellValue(info.getBreakerProtect5Name());

					HSSFCell cell24 = row.createCell(18);
					cell24.setCellStyle(getContentStyle(info
							.getBreakerProtect5DatagramInfo()));
					cell24.setCellValue(IntegerToString(info
							.getBreakerProtect5DatagramInfo()));

					HSSFCell cell25 = row.createCell(19);
					cell25.setCellStyle(getContentStyle(info
							.getBreakerProtect5WaveInfo()));
					cell25.setCellValue(IntegerToString(info
							.getBreakerProtect5WaveInfo()));

					HSSFCell cell26 = row.createCell(17); // 断路器保护六
					cell26.setCellStyle(contentStyle);
					cell26.setCellValue(info.getBreakerProtect6Name());

					HSSFCell cell27 = row.createCell(18);
					cell27.setCellStyle(getContentStyle(info
							.getBreakerProtect6DatagramInfo()));
					cell27.setCellValue(IntegerToString(info
							.getBreakerProtect6DatagramInfo()));

					HSSFCell cell28 = row.createCell(19);
					cell28.setCellStyle(getContentStyle(info
							.getBreakerProtect6WaveInfo()));
					cell28.setCellValue(IntegerToString(info
							.getBreakerProtect6WaveInfo()));
					
					
					HSSFCell cell35 = row.createCell(29);
					cell35.setCellValue(dto.getTotalIntactRate()+"%");
					cell35.setCellStyle(contentStyle);
					
					HSSFCell cell36 = row.createCell(30);
					cell36.setCellValue("");
					cell36.setCellStyle(contentStyle);
					

					HSSFCell cell37 = row.createCell(31);
					cell37.setCellValue(dto.getProtectEventIntactRate()+"%");
					cell37.setCellStyle(contentStyle);
					
					HSSFCell cell38 = row.createCell(32);
					cell38.setCellValue("");
					cell38.setCellStyle(contentStyle);
					
					
					HSSFCell cell39 = row.createCell(33);
					cell39.setCellValue(dto.getProtectWaveIntactRate()+"%");
					cell39.setCellStyle(contentStyle);

					
					HSSFCell cell40 = row.createCell(34);
					cell40.setCellValue("");
					cell40.setCellStyle(contentStyle);
					
					HSSFCell cell41 = row.createCell(35);
					cell41.setCellValue(dto.getCentralWave()+"%");
					cell41.setCellStyle(contentStyle);
					
					HSSFCell cell42 = row.createCell(36);
					cell42.setCellValue("");
					cell42.setCellStyle(contentStyle);
				}
			}
		}
	}

	/**
	 * 变压器sheet
	 */
	private static void writeTransFault(HSSFWorkbook workbook,
			List<FaultUploadInfoDTO> transFaultUploadInfoDtos) {
		// 1.1创建合并单元格对象
		List<CellRangeAddress> headers = new ArrayList<CellRangeAddress>();
		// 标题
		headers.add(new CellRangeAddress(0, 1, 0, 1)); // 起始行,结束行,起始列,结束列
		headers.add(new CellRangeAddress(0, 1, 2, 3));
		headers.add(new CellRangeAddress(0, 1, 4, 5));
		headers.add(new CellRangeAddress(0, 1, 6, 6));
		headers.add(new CellRangeAddress(0, 0, 7, 8));
		headers.add(new CellRangeAddress(0, 0, 9, 10));
		headers.add(new CellRangeAddress(0, 0, 11, 12));
		headers.add(new CellRangeAddress(0, 0, 13, 15));
		headers.add(new CellRangeAddress(0, 0, 16, 18));
		headers.add(new CellRangeAddress(0, 0, 19, 21));
		headers.add(new CellRangeAddress(0, 1, 22, 23));
		headers.add(new CellRangeAddress(0, 1, 24, 25));
		headers.add(new CellRangeAddress(0, 1, 26, 27));
		headers.add(new CellRangeAddress(0, 1, 28, 29));
		// 2.创建工作表
		HSSFSheet sheet = workbook.createSheet("变压器故障");
		// 2.1加载合并单元格对象
		for (CellRangeAddress ra : headers) {
			sheet.addMergedRegion(ra);
		}
		// 设置默认列宽
		sheet.setDefaultColumnWidth(9);
		sheet.setDefaultRowHeightInPoints(20F);

		// 3.2创建列标题;并且设置列标题
		HSSFRow row2 = sheet.createRow(0);
		String[] titles = { "故障时间", "", "故障情况", "", "厂站", "", "集中录播", "主一保护",
				"", "主二保护", "", "非电量保护", "", "500kV断路器保护一", "", "",
				"500kV断路器保护二", "", "", "220kV断路器保护一", "", "","综合上报完好率","",
				"保护事件完好率","","保护录波完好率","","集中录波完好率","" };
		for (int i = 0; i < titles.length; i++) {
			HSSFCell cell2 = row2.createCell(i);
			// 加载单元格样式
			cell2.setCellStyle(headerStyle);
			cell2.setCellValue(titles[i]);
		}

		HSSFRow rowfour = sheet.createRow(1);
		String[] titlefour = { "", "", "", "", "报文", "录波", "报文", "录波", "报文",
				"录波", "名称", "报文", "录波", "名称", "报文", "录波", "名称", "报文", "录波","","","","","","","",""   };
		for (int i = 0; i < titlefour.length; i++) {
			HSSFCell cell2 = rowfour.createCell(i + 3);
			// 加载单元格样式
			cell2.setCellStyle(headerStyle);
			cell2.setCellValue(titlefour[i]);
		}

		if (transFaultUploadInfoDtos != null) {
			int i = 0;
			int start = 2; // 每一条数据的开始行
			for (int j = 0; j < transFaultUploadInfoDtos.size(); j++) {
				i++;
				FaultUploadInfoDTO dto = transFaultUploadInfoDtos.get(j);
				List<StationFaultUploadInfoDTO> infos = dto.getStationInfo();
				if (infos.size() > 2) {
					for (int k = 0; k < 2; k++) {
						CellRangeAddress r2 = new CellRangeAddress(start, start
								+ infos.size() - 1, k * 2, k * 2 + 1);
						sheet.addMergedRegion(r2);
					}
					for (int k = 11; k < 15; k++) {
						CellRangeAddress r2 = new CellRangeAddress(start,
								start + 1, k*2 , k*2 + 1);
						sheet.addMergedRegion(r2);
					}
					start += infos.size();
				} else {
					for (int k = 0; k < 2; k++) {
						CellRangeAddress r2 = new CellRangeAddress(start,
								start + 1, k * 2, k * 2 + 1);
						sheet.addMergedRegion(r2);
					}
					for (int k = 11; k < 15; k++) {
						CellRangeAddress r2 = new CellRangeAddress(start,
								start + 1, k*2 , k*2 + 1);
						sheet.addMergedRegion(r2);
					}
					start += 2;
				}

				for (int k = 0; k <= 1; k++) {
					CellRangeAddress r3_1 = new CellRangeAddress(i * 2 + k, i
							* 2 + k, 4, 5); // 厂站
					sheet.addMergedRegion(r3_1);
				}
				if (infos.size() == 0) {
					HSSFRow row0 = sheet.createRow(start - 2); // 内容填充时需要返回到数据的开始行
					HSSFCell cell0 = row0.createCell(0);
					cell0.setCellStyle(contentStyle);
					cell0.setCellValue(DateUtil.getStringDate(
							dto.getFaultTime(), DateUtil.pattern_ymdhms));

					HSSFCell cell1 = row0.createCell(1);
					cell1.setCellStyle(contentStyle);
					cell1.setCellValue("");

					HSSFCell cell2 = row0.createCell(2);
					cell2.setCellStyle(contentStyle);
					cell2.setCellValue(dto.getFaultDesc());

					HSSFCell cell3 = row0.createCell(3);
					cell3.setCellStyle(contentStyle);
					cell3.setCellValue("");
				}

				for (int k = 0; k < infos.size(); k++) {
					StationFaultUploadInfoDTO info = infos.get(k);
					int n = infos.size();
					if (infos.size() == 1) {
						n = 2;
					}
					HSSFRow row = sheet.createRow(start - n + k);
					HSSFCell cell_0 = row.createCell(0);
					cell_0.setCellStyle(contentStyle);
					cell_0.setCellValue(DateUtil.getStringDate(
							dto.getFaultTime(), DateUtil.pattern_ymdhms));

					HSSFCell cell_1 = row.createCell(1);
					cell_1.setCellStyle(contentStyle);
					cell_1.setCellValue("");

					HSSFCell cell_2 = row.createCell(2);
					cell_2.setCellStyle(contentStyle);
					cell_2.setCellValue(dto.getFaultDesc());

					HSSFCell cell_3 = row.createCell(3);
					cell_3.setCellStyle(contentStyle);
					cell_3.setCellValue("");

					HSSFCell cell4 = row.createCell(4);
					cell4.setCellStyle(contentStyle);
					cell4.setCellValue(info.getStationName());

					HSSFCell cell5 = row.createCell(5);
					cell5.setCellStyle(contentStyle);
					cell5.setCellValue("");

					HSSFCell cell6 = row.createCell(6);
					cell6.setCellStyle(getContentStyle(info.getWaveInfo()));
					cell6.setCellValue(IntegerToString(info.getWaveInfo()));

					HSSFCell cell7 = row.createCell(7);
					cell7.setCellStyle(getContentStyle(info
							.getMain1DatagramInfo()));
					cell7.setCellValue(IntegerToString(info
							.getMain1DatagramInfo()));

					HSSFCell cell8 = row.createCell(8);
					cell8.setCellStyle(getContentStyle(info.getMain1WaveInfo()));
					cell8.setCellValue(IntegerToString(info.getMain1WaveInfo()));

					HSSFCell cell9 = row.createCell(9);
					cell9.setCellStyle(getContentStyle(info
							.getMain2DatagramInfo()));
					cell9.setCellValue(IntegerToString(info
							.getMain2DatagramInfo()));

					HSSFCell cell10 = row.createCell(10);
					cell10.setCellStyle(getContentStyle(info.getMain2WaveInfo()));
					cell10.setCellValue(IntegerToString(info.getMain2WaveInfo()));

					HSSFCell cell11 = row.createCell(11); // 非电量保护
					cell11.setCellStyle(getContentStyle(info
							.getNonElectricityProtectionDatagramInfo()));
					cell11.setCellValue(IntegerToString(info
							.getNonElectricityProtectionDatagramInfo()));

					HSSFCell cell12 = row.createCell(12);
					cell12.setCellStyle(getContentStyle(info
							.getNonElectricityProtectionWaveInfo()));
					cell12.setCellValue(IntegerToString(info
							.getNonElectricityProtectionWaveInfo()));

					HSSFCell cell13 = row.createCell(13); // 500kV断路器保护一
					cell13.setCellStyle(contentStyle);
					cell13.setCellValue(info.getBreakerProtect1Name());

					HSSFCell cell14 = row.createCell(14);
					cell14.setCellStyle(getContentStyle(info
							.getBreakerProtect1DatagramInfo()));
					cell14.setCellValue(IntegerToString(info
							.getBreakerProtect1DatagramInfo()));

					HSSFCell cell15 = row.createCell(15);
					cell15.setCellStyle(getContentStyle(info
							.getBreakerProtect1WaveInfo()));
					cell15.setCellValue(IntegerToString(info
							.getBreakerProtect1WaveInfo()));

					HSSFCell cell16 = row.createCell(16); // 500kV断路器保护二
					cell16.setCellStyle(contentStyle);
					cell16.setCellValue(info.getBreakerProtect2Name());

					HSSFCell cell17 = row.createCell(17);
					cell17.setCellStyle(getContentStyle(info
							.getBreakerProtect2DatagramInfo()));
					cell17.setCellValue(IntegerToString(info
							.getBreakerProtect2DatagramInfo()));

					HSSFCell cell18 = row.createCell(18);
					cell18.setCellStyle(getContentStyle(info
							.getBreakerProtect2WaveInfo()));
					cell18.setCellValue(IntegerToString(info
							.getBreakerProtect2WaveInfo()));

					HSSFCell cell19 = row.createCell(19); // 220kV断路器保护一
					cell19.setCellStyle(contentStyle);
					cell19.setCellValue(info.getBreakerProtect3Name());

					HSSFCell cell20 = row.createCell(20);
					cell20.setCellStyle(getContentStyle(info
							.getBreakerProtect3DatagramInfo()));
					cell20.setCellValue(IntegerToString(info
							.getBreakerProtect3DatagramInfo()));

					HSSFCell cell21 = row.createCell(21);
					cell21.setCellStyle(getContentStyle(info
							.getBreakerProtect3WaveInfo()));
					cell21.setCellValue(IntegerToString(info
							.getBreakerProtect3WaveInfo()));
					//TODO 断路器
					HSSFCell cell35 = row.createCell(22);
					cell35.setCellValue(dto.getTotalIntactRate()+"%");
					cell35.setCellStyle(contentStyle);
					
					HSSFCell cell36 = row.createCell(23);
					cell36.setCellValue("");
					cell36.setCellStyle(contentStyle);
					

					HSSFCell cell37 = row.createCell(24);
					cell37.setCellValue(dto.getProtectEventIntactRate()+"%");
					cell37.setCellStyle(contentStyle);
					
					HSSFCell cell38 = row.createCell(25);
					cell38.setCellValue("");
					cell38.setCellStyle(contentStyle);
					
					
					HSSFCell cell39 = row.createCell(26);
					cell39.setCellValue(dto.getProtectWaveIntactRate()+"%");
					cell39.setCellStyle(contentStyle);

					
					HSSFCell cell40 = row.createCell(27);
					cell40.setCellValue("");
					cell40.setCellStyle(contentStyle);
					
					HSSFCell cell41 = row.createCell(28);
					cell41.setCellValue(dto.getCentralWave()+"%");
					cell41.setCellStyle(contentStyle);
					
					HSSFCell cell42 = row.createCell(29);
					cell42.setCellValue("");
					cell42.setCellStyle(contentStyle);
				}
			}
		}
	}

	/**
	 * 汇总sheet
	 */
	private static void writeSummarySheet(HSSFWorkbook workbook,
			List<FaultSourceCountDTO> countList) {
		// 1.1创建合并单元格对象
		List<CellRangeAddress> headers = new ArrayList<CellRangeAddress>();
		// 标题
		headers.add(new CellRangeAddress(0, 2, 0, 1)); // 起始行,结束行,起始列,结束列
		headers.add(new CellRangeAddress(0, 0, 2, 7));
		headers.add(new CellRangeAddress(0, 1, 8, 10));
		headers.add(new CellRangeAddress(1, 1, 2, 4));
		headers.add(new CellRangeAddress(1, 1, 5, 7));

		// 2.创建工作表
		HSSFSheet sheet = workbook.createSheet("汇总");
		// 2.1加载合并单元格对象
		for (CellRangeAddress ra : headers) {
			sheet.addMergedRegion(ra);
		}
		// 设置默认列宽
		sheet.setDefaultColumnWidth(9);
		sheet.setDefaultRowHeightInPoints(20F);

		// 3.2创建列标题;并且设置列标题
		HSSFRow row2 = sheet.createRow(0);
		String[] titles = { "统计时间", "", "保护上送情况", "", "", "", "", "",
				"集中录波上送情况" };
		for (int i = 0; i < titles.length; i++) {
			HSSFCell cell2 = row2.createCell(i);
			// 加载单元格样式
			cell2.setCellStyle(headerStyle);
			cell2.setCellValue(titles[i]);
		}

		HSSFRow rowTwo = sheet.createRow(1);
		String[] titleTwo = { "", "", "报文", "", "", "录波" };
		for (int i = 0; i < titleTwo.length; i++) {
			HSSFCell cell2 = rowTwo.createCell(i);
			// 加载单元格样式
			cell2.setCellStyle(headerStyle);
			cell2.setCellValue(titleTwo[i]);
		}

		HSSFRow rowThree = sheet.createRow(2);
		String[] titleThree = { "", "", "完整次数", "不完整次数", "完整率", "完整次数",
				"不完整次数", "完整率", "完整次数", "不完整次数", "完整率" };
		for (int i = 0; i < titleThree.length; i++) {
			HSSFCell cell2 = rowThree.createCell(i);
			// 加载单元格样式
			cell2.setCellStyle(headerStyle);
			cell2.setCellValue(titleThree[i]);
		}

		if (countList != null) {
			for (int j = 0; j < countList.size(); j++) {
				int rowNum = 3 + j;
				CellRangeAddress r = new CellRangeAddress(rowNum, rowNum, 0, 1); // 厂站
				sheet.addMergedRegion(r);
				FaultSourceCountDTO dto = countList.get(j);
				HSSFRow row = sheet.createRow(rowNum);

				HSSFCell cell1 = row.createCell(0);
				cell1.setCellStyle(contentStyle);
				cell1.setCellValue(dto.getYear() + "年" + dto.getMonth() + "月");

				HSSFCell cell2 = row.createCell(1);
				cell2.setCellStyle(contentStyle);
				cell2.setCellValue("");

				HSSFCell cell3 = row.createCell(2);
				cell3.setCellStyle(contentStyle);
				cell3.setCellValue(dto.getActionDatagramCompleteCount());

				HSSFCell cell4 = row.createCell(3);
				cell4.setCellStyle(contentStyle);
				cell4.setCellValue(dto.getActionDatagramIncompleteCount());

				HSSFCell cell5 = row.createCell(4);
				cell5.setCellStyle(contentStyle);
				cell5.setCellValue(getRate(
						dto.getActionDatagramCompleteCount(),
						dto.getActionDatagramIncompleteCount()));

				HSSFCell cell6 = row.createCell(5);
				cell6.setCellStyle(contentStyle);
				cell6.setCellValue(dto.getActionWaveCompleteCount());

				HSSFCell cell7 = row.createCell(6);
				cell7.setCellStyle(contentStyle);
				cell7.setCellValue(dto.getActionWaveIncompleteCount());

				HSSFCell cell8 = row.createCell(7);
				cell8.setCellStyle(contentStyle);
				cell8.setCellValue(getRate(dto.getActionWaveCompleteCount(),
						dto.getActionWaveIncompleteCount()));

				HSSFCell cell9 = row.createCell(8);
				cell9.setCellStyle(contentStyle);
				cell9.setCellValue(dto.getWaveCompleteCount());

				HSSFCell cell10 = row.createCell(9);
				cell10.setCellStyle(contentStyle);
				cell10.setCellValue(dto.getWaveIncompleteCount());

				HSSFCell cell11 = row.createCell(10);
				cell11.setCellStyle(contentStyle);
				cell11.setCellValue(getRate(dto.getWaveCompleteCount(),
						dto.getWaveIncompleteCount()));
			}
		}
	}

	private static String IntegerToString(Integer i) {
		if(null == i) {
			return "";
		} else if (-1==i) {
			return "不完整";
		} else {
			return "完整";
		}
	}
	
	private static String getRate(int completeCount, int inCompleteCount) {
		if (0 == completeCount) {
			return "0%";
		} else if (0 == inCompleteCount) {
			return "100%";
		}
		Float c1 = Float.valueOf(completeCount);
		Float c2 = Float.valueOf(inCompleteCount);
		return String.format("%.2f", c1 / (c2 + c1) * 100) + "%";
	}

	/**
	 * 
	 * @param workbook
	 * @param fontsize
	 * @return 单元格样式
	 */
	private static HSSFCellStyle createCellStyle(HSSFWorkbook workbook,
			short fontsize, boolean flag, boolean flag1) {
		HSSFCellStyle style = workbook.createCellStyle();
		// 是否水平居中
		if (flag1) {
			style.setAlignment(HorizontalAlignment.CENTER);// 水平居中
		}

		style.setVerticalAlignment(VerticalAlignment.CENTER);// 垂直居中
		// 创建字体
		HSSFFont font = workbook.createFont();
		// 是否加粗字体
		if (flag) {
			font.setBold(true);
		}
		font.setFontHeightInPoints(fontsize);
		// 加载字体
		style.setFont(font);
		return style;
	}

	private static HSSFCellStyle creatGreenCellStyle(HSSFWorkbook workbook,
			short fontsize, boolean flag, boolean flag1) {
		HSSFCellStyle style = workbook.createCellStyle();
		// 是否水平居中
		if (flag1) {
			style.setAlignment(HorizontalAlignment.CENTER);// 水平居中
		}

		style.setVerticalAlignment(VerticalAlignment.CENTER);// 垂直居中
		// 创建字体
		HSSFFont font = workbook.createFont();
		font.setColor(HSSFColor.SEA_GREEN.index);
		// 是否加粗字体
		if (flag) {
			font.setBold(true);
		}
		font.setFontHeightInPoints(fontsize);
		// 加载字体
		style.setFont(font);
		return style;
	}

	private static HSSFCellStyle createRedCellStyle(HSSFWorkbook workbook,
			short fontsize, boolean flag, boolean flag1) {
		HSSFCellStyle style = workbook.createCellStyle();
		// 是否水平居中
		if (flag1) {
			style.setAlignment(HorizontalAlignment.CENTER);// 水平居中
		}

		style.setVerticalAlignment(VerticalAlignment.CENTER);// 垂直居中
		// 创建字体
		HSSFFont font = workbook.createFont();
		// 是否加粗字体
		if (flag) {
			font.setBold(true);
		}
		font.setColor(HSSFColor.RED.index);
		font.setFontHeightInPoints(fontsize);
		// 加载字体
		style.setFont(font);
		return style;
	}

	public static void exportFaultSourceCountByYear(List<YearCountInfo> yearCountInfos,FileOutputStream fos) {
		HSSFWorkbook workbook = new HSSFWorkbook();
		HSSFSheet sheet = workbook.createSheet("故障信息上送率年统计");
		String[] columnName = {"单位","类型","1月","2月","3月","4月","5月","6月","7月","8月","9月","10月","11月","12月"};
		contentStyle = createCellStyle(workbook, (short) 11, false, true);
		headerStyle = createCellStyle(workbook, (short) 11, true, true);
		HSSFRow row0 = sheet.createRow(0);
		for (int i = 0; i < columnName.length; i++) {
			HSSFCell cell = row0.createCell(i);
			// 加载单元格样式
			cell.setCellStyle(headerStyle);
			cell.setCellValue(columnName[i]);
		}
		
		if (yearCountInfos != null) {
			int i=0;
			for (YearCountInfo info : yearCountInfos) {
				HSSFRow row = sheet.createRow((int) i + 1);
				// 单位
				HSSFCell cell0 =  row.createCell(0);
				cell0.setCellValue(info.getDeptName());
				cell0.setCellStyle(contentStyle);
				// 数据类型
				HSSFCell cell1 =  row.createCell(1);
				cell1.setCellValue(info.getType());
				cell1.setCellStyle(contentStyle);
				Map<Integer, List<String>> uploadInfoMap = info.getUploadRatio();
				for (Integer month : uploadInfoMap.keySet()) {
					HSSFCell cell =  row.createCell(month+1);
					cell.setCellValue(uploadInfoMap.get(month).get(3)+"%");
					cell.setCellStyle(contentStyle);
				}
				i++;
			}
		}
		try {
			workbook.write(fos);
			workbook.close();
		} catch (IOException e) {
			e.printStackTrace();
		}
	}
	
	public static void exportFaultSourceCountByMonth(List<FaultSourceCountVO> items,FileOutputStream os) {
		HSSFWorkbook workbook = new HSSFWorkbook();
		contentStyle = createCellStyle(workbook, (short) 11, false, true);
		headerStyle = createCellStyle(workbook, (short) 11, true, true);
		HSSFSheet sheet = workbook.createSheet("故障信息上送率月统计");
		HSSFRow row0 = sheet.createRow(0);
		String[] title1 = { "数据类型","上报单位","报送情况", "", "", "", "", "" };
		
		for (int i = 0; i < title1.length; i++) {
			HSSFCell cell = row0.createCell(i);
			// 加载单元格样式
			cell.setCellStyle(headerStyle);
			cell.setCellValue(title1[i]);
		}

		HSSFRow row1 = sheet.createRow(1);
		String[] title2 = {"上送率","应上报次数","实际上送次数","自动上送次数","人工上送次数"};
		for (int i = 0; i < title2.length; i++) {
			HSSFCell cell = row1.createCell(i + 2);
			// 加载单元格样式
			cell.setCellStyle(headerStyle);
			cell.setCellValue(title2[i]);
		}
		sheet.addMergedRegion(new CellRangeAddress(0, 1, 0, 0)); // 起始行,结束行,起始列,结束列
		sheet.addMergedRegion(new CellRangeAddress(0, 1, 1, 1)); // 起始行,结束行,起始列,结束列
		sheet.addMergedRegion(new CellRangeAddress(0, 0, 2, 6));
		if (items != null) {
			int i=2;
			for (FaultSourceCountVO vo : items) {
				List<UnitCountInfo> lt = vo.getUnits();
				for (UnitCountInfo info : lt) {
					HSSFRow row = sheet.createRow(i);
					// 数据类型
					HSSFCell cell0 = row.createCell(0);
					cell0.setCellValue(vo.getType());
					cell0.setCellStyle(contentStyle);
					// 单位名称
					HSSFCell cell1 = row.createCell(1);
					cell1.setCellValue(info.getName());
					cell1.setCellStyle(contentStyle);
					// 上送率
					HSSFCell cell2 = row.createCell(2);
					cell2.setCellValue(info.getUploadRatio()+"%");
					cell2.setCellStyle(contentStyle);
					// 应上送次数
					HSSFCell cell3 = row.createCell(3);
					cell3.setCellValue(info.getTargetTotal());
					cell3.setCellStyle(contentStyle);
					// 实际上送次数
					HSSFCell cell4 = row.createCell(4);
					cell4.setCellValue(info.getUpTotal());
					cell4.setCellStyle(contentStyle);
					// 自动上送次数
					HSSFCell cell5 = row.createCell(5);
					cell5.setCellValue(info.getAutomatic());
					cell5.setCellStyle(contentStyle);
					// 人工上送次数
					HSSFCell cell6 = row.createCell(6);
					cell6.setCellValue(info.getManual());
					cell6.setCellStyle(contentStyle);
					i++;
				}
				// 总计
				
				HSSFRow row = sheet.createRow(i);
				sheet.addMergedRegion(new CellRangeAddress(i, i, 0, 1));
				
				HSSFCell cell0 = row.createCell(0);
				cell0.setCellValue("总计：");
				cell0.setCellStyle(contentStyle);
				UnitCountInfo total = vo.getTotal();
				
				HSSFCell cell1 = row.createCell(2);
				cell1.setCellValue(total.getUploadRatio()+"%");
				cell1.setCellStyle(contentStyle);
				
				HSSFCell cell2 = row.createCell(3);
				cell2.setCellValue(total.getTargetTotal());
				cell2.setCellStyle(contentStyle);
				
				HSSFCell cell3 = row.createCell(4);
				cell3.setCellValue(total.getUpTotal());
				cell3.setCellStyle(contentStyle);
				
				HSSFCell cell4 = row.createCell(5);
				cell4.setCellValue(total.getAutomatic());
				cell4.setCellStyle(contentStyle);
				
				HSSFCell cell5 = row.createCell(6);
				cell5.setCellValue(total.getManual());
				cell5.setCellStyle(contentStyle);
				i++;
			}
		}
		try {
			workbook.write(os);
			workbook.close();
		} catch (IOException e) {
			e.printStackTrace();
		}
	}
	
	public static void main(String[] args) {
		List<FaultSourceInfoDTO> userList = new ArrayList<FaultSourceInfoDTO>();
		FileOutputStream fos = null;
		for (int i = 0; i < 10; i++) {
			userList.add(new FaultSourceInfoDTO());
		}

		try {
			fos = new FileOutputStream(new File("d:/t.xls"));
			// getValue(userList, fos);
		} catch (FileNotFoundException e) {
			e.printStackTrace();
		} finally {

			try {
				if (fos != null)
					fos.close();
			} catch (IOException e) {
				e.printStackTrace();
			}
		}

	}

}
